import pandas as pd

# 读取data_all_2024080501.xlsx文件
df = pd.read_excel(r'D:\python-ml-learn\实验3\d2.xlsx')

# 查询no = 12 、时间为八月一日的，每小时中KWH的最大值和最小值（以八月一日七点到八月二日七点的数据为八月一日的）
 # 转换时间列的格式2024/8/1 1:35:00  2024-08-01 01:35:00
# df0 = df[(df['NO'] == 12) & (df['date'].dt.strftime("%Y-%m-%d") == '2024-08-01')]
df0 = df[(df['NO'] == 12) & (df['date'].dt.strftime("%Y-%m-%d %H") >= '2024-08-01 07') & (df['date'].dt.strftime("%Y-%m-%d %H") < '2024-08-02 07')]
print(df0.tail(10))

# dates数据格式如：2024/8/1 1:35:00，获取每小时内的KWH的最大值和最小值
df0 = df0.set_index('date')
df_max = df0.resample('h')['KWH'].max()
df_min = df0.resample('h')['KWH'].min()
# 合并最大值列和最小值列
df_max_min = pd.concat([df_max, df_min], axis=1,keys=['max', 'min'])
print(df_max_min)

r = df_max - df_min
print(r)
# 差值合并到df_max_min
df_max_min['diff'] = r
print(df_max_min)





